NOTE: PROCEDURE PRINTTO used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

4    
5    * Must process each year separately. The NPX file is large, it will take a long time to
5  ! process each year.
6    * After processing all years, use the code in Part 2 and 3 to append annual data and make
6  ! the voting variable.
7    * This code uses 2006 as an example. ;
8    
9    DM "log; clear; ";
10   DM 'odsresults; clear';
11   
12   %global projectrootdirectory;
13   %let projectrootdirectory=C:/Users/hpham13/Projects/mf_activism/empirical;
14   
15   *Creates your local Libraries;
16   libname rawdata "&projectrootdirectory./0_input/original";
NOTE: Libref RAWDATA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Projects\mf_activism\empirical\0_input\original
17   libname rawdata3 "C:/Users/hpham13/Projects/mf_activism/empirical/1_code/python/";
NOTE: Libref RAWDATA3 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Projects\mf_activism\empirical\1_code\python
18   libname rawdata4
18 ! "C:/Users/hpham13/Projects/mf_activism/empirical/1_code/python/npx_blockholdervote_results/"
18 ! ;
NOTE: Libref RAWDATA4 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: 
      C:\Users\hpham13\Projects\mf_activism\empirical\1_code\python\npx_blockholdervote_results
19   libname save    "&projectrootdirectory./1_code/final_package_acceptance";
NOTE: Libref SAVE was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: 
      C:\Users\hpham13\Projects\mf_activism\empirical\1_code\final_package_acceptance
20   
21   %global datadirectory;
22   %let datadirectory=C:/Users/hpham13/Data;
23   
24   /* Define the local library */
25   libname data "F:/Data/crsp/crsp_q_mutualfunds";
NOTE: Libref DATA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: F:\Data\crsp\crsp_q_mutualfunds
26   libname compa "&datadirectory./compustat/funda/";
NOTE: Libref COMPA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\compustat\funda
27   libname lnk "&datadirectory./Linking_tables/";
NOTE: Libref LNK was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\Linking_tables
28   libname ceo "&datadirectory./ceo/";
NOTE: Libref CEO was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\ceo
29   libname activism "&datadirectory./activism/" ;
NOTE: Libref ACTIVISM was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\activism
30   libname comp "&datadirectory./compustat/fundq/";
NOTE: Libref COMP was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\compustat\fundq
31   libname npx "F:/Data/Npx/";
NOTE: Libref NPX was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: F:\Data\Npx
32   libname crsp "&datadirectory./crsp/";
NOTE: Libref CRSP was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\crsp
33   libname tr "&datadirectory./ThomsonReuters/s12";
NOTE: Libref TR was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\ThomsonReuters\s12
34   libname lnk "&datadirectory./Linking_tables";
NOTE: Libref LNK was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\Linking_tables
35   libname mfl "&datadirectory./Linking_tables/mflink2_20230828";
NOTE: Libref MFL was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\hpham13\Data\Linking_tables\mflink2_20230828
36   
37   * include some macros;
38   %include "&datadirectory./utils/do_over.sas";
345  %include "&datadirectory./utils/ff48.sas";
417  %include "&datadirectory./utils/ff49.sas";
490  %include "&datadirectory./utils/ff12.sas";
529  %include "&datadirectory./utils/winsorize.sas";
617   %include "&datadirectory./utils/lags.sas";
796   
797   
798   /* ******************************************************************************** */
799   /*                          PART 1: LINK GVKEY & LPERMNO                            */
800   /* ******************************************************************************** */
801   %let begdate = 01MAR1995;
802   %let enddate = 31DEC2021;
803   %let vars=  gvkey fyearq fqtr fyr rdq conm datadate indfmt datafmt popsrc consol saleq atq;
804   data fundq;
805      set comp.fundq_1950_2021 (keep= &vars);
806      where datadate between "&begdate"d and "&enddate"d ;
807      if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C';
808      format endfyr begfyr date9.;
809      endfyr= datadate;
810      begfyr= intnx('month',endfyr,-11,'beg');
811   run;

NOTE: There were 1240531 observations read from the data set COMP.FUNDQ_1950_2021.
      WHERE (datadate>='01MAR1995'D and datadate<='31DEC2021'D);
NOTE: The data set WORK.FUNDQ has 1236959 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           3.87 seconds
      cpu time            0.48 seconds
      

812   proc sort; by gvkey endfyr; run;

NOTE: There were 1236959 observations read from the data set WORK.FUNDQ.
NOTE: The data set WORK.FUNDQ has 1236959 observations and 15 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.22 seconds
      cpu time            0.39 seconds
      

813   
814   * Make a link GVKEYS to CRSP-PERMNO;
815   proc sql;
816     create table compx2 as select *
817     from fundq as a, lnk.ccmxpf_lnkhist as b
818     where a.gvkey = b.gvkey and
819     b.LINKTYPE in ("LU","LC","LD","LN","LS","LX") and
820     (b.LINKDT <= a.endfyr or b.LINKDT = .B) and (a.endfyr <= b.LINKENDDT or b.LINKENDDT = .E)
820 ! ;
WARNING: Variable GVKEY already exists on file WORK.COMPX2.
NOTE: Table WORK.COMPX2 created, with 808675 rows and 22 columns.

821   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.19 seconds
      cpu time            0.21 seconds
      

822   
823   data compx3 ;
824       set compx2;
825       keep gvkey LPERMNO fyearq ;
826   run;

NOTE: There were 808675 observations read from the data set WORK.COMPX2.
NOTE: The data set WORK.COMPX3 has 808675 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.06 seconds
      

827   
828   proc sort data=compx3 nodupkey; by gvkey fyearq; run;

NOTE: There were 808675 observations read from the data set WORK.COMPX3.
NOTE: 599933 observations with duplicate key values were deleted.
NOTE: The data set WORK.COMPX3 has 208742 observations and 3 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.05 seconds
      cpu time            0.11 seconds
      

829   
830   
831   /* ******************************************************************************** */
832   /*                      PART 2: IMPORT DATA                                         */
833   /* ******************************************************************************** */
834   * Holding data;
835   data holdings; set rawdata3.Mf_merged3_042825; run;

NOTE: There were 14308623 observations read from the data set RAWDATA3.MF_MERGED3_042825.
NOTE: The data set WORK.HOLDINGS has 14308623 observations and 28 variables.
NOTE: DATA statement used (Total process time):
      real time           22.98 seconds
      cpu time            2.67 seconds
      

836   
837   
838   * NPX data;
839   * Note: must run each year separately. It takes a long time each year;
840   * Select the Year here;
841   %let year = 2006;
842   
843   * Import the NPX data - to get the fund-level vote data;
844   data npx;
845       set npx.npx_&year.; * until 2017;
846       *set npx.npx_&year._12092024; *from 2018;
847       keyID = InstitutionID||FundID;
848       drop version;
849   run;

NOTE: Numeric values have been converted to character values at the places given by: 
      (Line):(Column).
      847:13   847:28   
NOTE: There were 4312509 observations read from the data set NPX.NPX_2006.
NOTE: The data set WORK.NPX has 4312509 observations and 21 variables.
NOTE: DATA statement used (Total process time):
      real time           2:55.09
      cpu time            3.31 seconds
      

850   
851   
852   /* ******************************************************************************** */
853   /*                      PART 3: MERGE DATA                                          */
854   /* ******************************************************************************** */
855   * Import the hand checked name match data ;
856   proc import out=matchnames
857       datafile =
857 ! 'C:/Users/hpham13/Projects/mf_activism/empirical/1_code/python/name_matches_fund_level_0423
857 ! 2025.xlsx'
858       dbms = xlsx replace;
859       sheet="npx_active_match";
860   run;

NOTE: One or more variables were converted because the data type is not supported by the V9 
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 22672 observations and 11 variables.
NOTE: WORK.MATCHNAMES data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           1.61 seconds
      cpu time            1.57 seconds
      

861   
862   data matchnames&year.;
863       set matchnames;
864       where year = &year.;
865       keyID = InstitutionID||FundID;
866   run;

NOTE: Numeric values have been converted to character values at the places given by: 
      (Line):(Column).
      865:13   865:28   
NOTE: There were 1192 observations read from the data set WORK.MATCHNAMES.
      WHERE year=2006;
NOTE: The data set WORK.MATCHNAMES2006 has 1192 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

867   
868   * make the list of InstitutionID and FundID;
869   proc sql;
870       create table keyID as
871       select distinct keyID
872       from matchnames&year. ;
NOTE: Table WORK.KEYID created, with 1192 rows and 1 columns.

873   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

874   
875   * Import the vavote data to get meetingid;
876   data vavote;
877       set activism.vavoteresults_12092024;
878       if year(meetingdate) = &year.;
879   run;

NOTE: There were 787289 observations read from the data set ACTIVISM.VAVOTERESULTS_12092024.
NOTE: The data set WORK.VAVOTE has 21246 observations and 29 variables.
NOTE: DATA statement used (Total process time):
      real time           1.36 seconds
      cpu time            0.18 seconds
      

880   proc sql;
881       create table vavote_meetingid as
882       select distinct meetingid
883       from vavote;
NOTE: Table WORK.VAVOTE_MEETINGID created, with 3080 rows and 1 columns.

884   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

885   
886   * subset npx data;
887   proc sql;
888       create table npx2 as
889       select  * from npx
890       where   meetingid in (select * from vavote_meetingid) and
891               keyID in (select * from keyID) ;
NOTE: Table WORK.NPX2 created, with 694472 rows and 21 columns.

892   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           4.15 seconds
      cpu time            3.54 seconds
      

893   
894   * Obtain Firm cusip;
895   proc sql;
896       create table npx3 as
897       select  a.*, b.cusip
898       from    npx2 as a join (select distinct CompanyID, CUSIP from vavote) as b
899       on      a.CompanyID=b.CompanyID ;
NOTE: Table WORK.NPX3 created, with 700655 rows and 22 columns.

900   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.01 seconds
      cpu time            0.96 seconds
      

901   
902   * Mege CRSP mgmt_name, fund_name, and wficn ;
903   proc sql;
904       create table npx3 as
905       select  a.*, b.mgmt_name, b.mgmt_cd, b.fund_name, b.wficn
906       from    npx3 as a left join (
907                   select distinct mgmt_name, mgmt_cd,
908                   fund_name, wficn, InstitutionName,
909                   FundName from Matchnames&year. ) as b
910       on      a.InstitutionName = b.InstitutionName and a.FundName = b.FundName ;
WARNING: This CREATE TABLE statement recursively references the target table. A consequence of 
         this is a possible data integrity problem.
NOTE: Table WORK.NPX3 created, with 700655 rows and 26 columns.

911   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           4.59 seconds
      cpu time            5.01 seconds
      

912   
913   * Merge with announce_date from activism data;
914   data pilot;
915       set activism.ca_details_95_pilot;
916       where year(MEETING_DATE) = &year. ;
917   run;

NOTE: There were 121 observations read from the data set ACTIVISM.CA_DETAILS_95_PILOT.
      WHERE YEAR(MEETING_DATE)=2006;
NOTE: The data set WORK.PILOT has 121 observations and 147 variables.
NOTE: DATA statement used (Total process time):
      real time           0.15 seconds
      cpu time            0.06 seconds
      

918   proc sort data=pilot nodupkey; by  gvkey CAMPAIGN_ID ; run;

NOTE: There were 121 observations read from the data set WORK.PILOT.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.PILOT has 121 observations and 147 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

919   
920   data pilot;
921       set pilot;
922       keep CAMPAIGN_ID ANNOUNCE_DATE FILING_DATE END_DATE MEETING_DATE CUSIP gvkey year ;
923       if  MEETING_DATE ne . ;
924   run;

NOTE: There were 121 observations read from the data set WORK.PILOT.
NOTE: The data set WORK.PILOT has 121 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

925   
926   * get permno;
927   proc sql;
928       create table pilot as
929       select  a.*, b.LPERMNO
930       from    pilot as a left join compx3 as b
931       on      a.gvkey=b.gvkey and
932               year(a.ANNOUNCE_DATE)=b.fyearq ;
WARNING: This CREATE TABLE statement recursively references the target table. A consequence of 
         this is a possible data integrity problem.
NOTE: Table WORK.PILOT created, with 121 rows and 9 columns.

933   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
      

934   
935   * merge campaign data and ISS voting data;
936   proc sql;
937       create  table vote_pilot_1 as
938       select  b.gvkey, b.LPERMNO, b.CAMPAIGN_ID, b.MEETING_DATE, a.*
939       from    npx3 as a join (select distinct gvkey, lpermno, cusip, campaign_id,
939 ! MEETING_DATE from  pilot) as b
940       on      a.cusip=b.cusip and b.MEETING_DATE=a.MeetingDate ;
NOTE: Table WORK.VOTE_PILOT_1 created, with 13995 rows and 30 columns.

941   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.38 seconds
      cpu time            0.32 seconds
      

942   
943   * save the output;
944   data save.vote_pilot_1_&year. ;
945       set vote_pilot_1;
946   run;

NOTE: There were 13995 observations read from the data set WORK.VOTE_PILOT_1.
NOTE: The data set SAVE.VOTE_PILOT_1_2006 has 13995 observations and 30 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

947   
948   
949   proc printto;
950   run;

